package com.woniu.goods.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.woniu.goods.domain.Goods;
import com.woniu.goods.domain.GoodsType;
import com.woniu.goods.util.JDBCUtil;
import com.woniu.goods.util.PageUtil;

public class GoodsDaoImpl implements IGoodsDao{
	JDBCUtil util = new JDBCUtil();
	
	@Override
	public int selectCount(String gname, int tid) {
		Connection conn = null ;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try {
			conn = util.getConnection();
			String sql = "select count(*) from t_goods g left join t_type t on g.tid = t.tid  where 1=1 " ;
			if (gname != null && !"".equals(gname.trim())) {
				sql += " and g.gname like '%"+gname+"%' " ;
			}
			if(tid != 0) {
				sql += " and g.tid = "+tid ;
			}
			pstm = conn.prepareStatement(sql);
			
			rs = pstm.executeQuery();
			
			if(rs.next()) {
				return rs.getInt(1);
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				util.release(rs, pstm, conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return 0;
	}

	@Override
	public List<Goods> selectList(PageUtil pu, String gname, int tid) {
		Connection conn = null ;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		List<Goods> lists = new ArrayList<>();
		
		try {
			conn = util.getConnection();
			String sql = "select * from t_goods g left join t_type t on g.tid = t.tid  where 1=1 " ;
			if (gname != null && !"".equals(gname.trim())) {
				sql += " and g.gname like '%"+gname+"%' " ;
			}
			if(tid != 0) {
				sql += " and g.tid = "+tid ;
			}
			
			sql += " limit "+pu.getStartIndex()+","+pu.getPageSize()+" " ;
			
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			
			while(rs.next()) {
				Goods goods = new Goods();
				goods.setGid(rs.getInt("gid"));
				goods.setGname(rs.getString("gname"));
				goods.setCount(rs.getInt("count"));
				goods.setImgPath(rs.getString("imgpath"));
				goods.setStatus(rs.getString("status"));
				goods.setPrice(rs.getDouble("price"));
				goods.setCreateDate(rs.getDate("createdate"));
				
				//根据类型id 查询一个类型对象
				int id = rs.getInt("tid");
				GoodsType type = this.selectTypeById(id);
				goods.setgType(type);
				
				lists.add(goods);
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				util.release(rs, pstm, conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return lists;
	}

	@Override
	public List<GoodsType> selectAllType() {
		Connection conn = null ;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		List<GoodsType> lists = new ArrayList<GoodsType>();
		
		try {
			conn = util.getConnection();
			String sql = "select * from t_type" ;
			pstm = conn.prepareStatement(sql);
			
			rs = pstm.executeQuery();
			
			while (rs.next()) {
				GoodsType type = new GoodsType(rs.getInt("tid"), rs.getString("tname"));
				lists.add(type);
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				util.release(rs, pstm, conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return lists;
	}

	@Override
	public GoodsType selectTypeById(int id) {
		Connection conn = null ;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		GoodsType type = null;
		
		try {
			conn = util.getConnection();
			String sql = "select * from t_type where tid = ?" ;
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, id);
			rs = pstm.executeQuery();
			
			if (rs.next()) {
				type = new GoodsType(rs.getInt("tid"), rs.getString("tname"));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				util.release(rs, pstm, conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return type ;
	}
	
	

}
